use master
if exists(select * from sys.databases where name='OnlineFloralDelivery') drop database OnlineFloralDelivery
go
create database OnlineFloralDelivery
go
use OnlineFloralDelivery
--use master
--drop database OnlineFloralDelivery
--create database OnlineFloralDelivery
go

create table Roles(
	RoleID int identity primary key,
	RoleName nvarchar(200) not null,
	Description nvarchar(max),
	IsEnable bit default 1 not null
)

create table Users(
	UserID int identity primary key,
	RoleID int foreign key references Roles(RoleID)	 not null,
	--UserName nvarchar(50) not null,
	Email nvarchar(200) not null,
	Password nvarchar(50) not null,
	CreateDate datetime not null default GetDate(),
	FirstName nvarchar(50) not null,
	LastName nvarchar(50) not null,
	Gender bit not null,
	Birthday datetime,
	Address nvarchar(max) not null,
	Phone nchar(15) not null,
	SecretQuestion nvarchar(200) not null,
	SecretAnswer nvarchar(200) not null,
	IsEnable bit not null default 1
)

create table Occasions(
	OccasionID int identity primary key,
	OccasionName nvarchar(200) not null,
	IsEnable bit default 1 not null
)

create table Bouquets(
	BouquetID int identity primary key,
	OccasionID int foreign key references Occasions(OccasionID) not null,
	[Name] nvarchar(200) not null,
	Description nvarchar(max),
	Price money not null,
	Quantity int not null,
	IsEnable bit default 1 not null
)

create table Messages(
	MessageID int identity primary key,
	OccasionID int foreign key references Occasions(OccasionID) not null,
	MessageContent nvarchar(max) not null,
	IsEnable bit default 1 not null
)

--create table Billing(
	--BillID int identity primary key,
	--UserID int foreign key references Users(UserID) not null,
	--BillDate datetime,
	--TotalAmount money not null
--)

create table Orders(
	OrderID int identity primary key,
--	ShippingID int foreign key references ShippingDetails(ShippingID) not null,
	UserID int foreign key references Users(UserID) not null,
	TotalAmount money not null,
	BillDate datetime,
	OrderDate datetime default GetDate() not null,
	IsEnable bit default 1 not null
)

create table ShippingDetails(
	OrderID int primary key foreign key references Orders(OrderID) not null,
	[Name] nvarchar(200) not null,
	Address nvarchar(max) not null,
	Phone nchar(20),
	MobilePhone nchar(20),
	Note nvarchar(max),
	[Message] nvarchar(max),
	DeliveryDate datetime not null,
)

create table OrderDetails(
	OrderID int foreign key references Orders(OrderID) not null,
	BouquetID int foreign key references Bouquets(BouquetID) not null,
	Quantity int not null,
	CurrentPrice money not null,
	primary key (OrderID, BouquetID)
)

create table News(
	NewsID int identity primary key,
	Title nvarchar(200) not null,
	[Content] nvarchar(max) not null,
	PostedDate datetime default GetDate() not null,
	IsEnable bit default 1 not null
)

create table Discount(
	BouquetID int primary key foreign key references Bouquets(BouquetID) not null,
	DiscountPercent float not null
)
go
insert into Roles(RoleName) values('admin')
insert into Occasions(OccasionName) values('occa 1')
insert into Bouquets(OccasionID,[Name],Price,Quantity)
values(1, 'afdf', 656.5, 45)
insert into Messages(OccasionID,MessageContent)
values(1, 'message demo')
declare @var int;
insert into News(Title,[Content])
values('new 2', 'content here'); set @var = scope_identity()
select @var as [OUT]
